package com.example.dao;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.example.domain.TermClassPrice;

@Repository
public interface TermClassPriceDao extends JpaRepository<TermClassPrice, Integer> {

    @Modifying
    @Transactional
    Integer deleteByTermClassPriceId(Integer id);

    List<TermClassPrice> findByTermClassPriceId(Integer id);

    @Modifying
    @Transactional
    Integer deleteByTermClassPriceIdIn(List<Integer> listId);

    @Query(nativeQuery = true, value = "SELECT t.termName,c.className,tcp.* FROM (term_class_price tcp INNER JOIN classes c \r\n" +
            "ON tcp.class_Id = c.class_Id)\r\n" +
            "INNER JOIN term t ON c.term_Id = t.term_Id\r\n" +
            "WHERE t.termName LIKE CONCAT('%',:termName,'%')")
    List<Map<String, Object>> findByTermNameLike(@Param("termName") String termName);

    @Query(nativeQuery = true, value = "SELECT t.termName,c.className,tcp.* FROM (term_class_price tcp INNER JOIN classes c \r\n" +
            "ON tcp.class_Id = c.class_Id)\r\n" +
            "INNER JOIN term t ON c.term_Id = t.term_Id\r\n" +
            "WHERE c.className LIKE CONCAT('%',:className,'%')")
    List<Map<String, Object>> findByClassNameLike(@Param("className") String className);

    @Query(nativeQuery = true, value = "SELECT t.termName,c.className,tcp.* FROM (term_class_price tcp INNER JOIN classes c \r\n" +
            "ON tcp.class_Id = c.class_Id)\r\n" +
            "INNER JOIN term t ON c.term_Id = t.term_Id\r\n" +
            "WHERE tcp.price LIKE CONCAT('%',:price,'%')")
    List<Map<String, Object>> findByPriceLike(@Param("price") String price);

    @Query(nativeQuery = true, value = "SELECT t.termName,c.className,tcp.* FROM (term_class_price tcp INNER JOIN classes c \r\n" +
            "ON tcp.class_Id = c.class_Id)\r\n" +
            "INNER JOIN term t ON c.term_Id = t.term_Id")
    List<Map<String, Object>> queryAllTermClassPriceInfo();

    @Query(nativeQuery = true, value = "SELECT c.class_Id as classId,t.termName,c.className \r\n" +
            "from classes c left join term t \r\n" +
            "on c.term_Id = t.term_Id\r\n" +
            "where c.class_Id not in (select tcp.class_Id from term_class_price tcp)")
    List<Map<String, Object>> queryAllTermClassInfo();


    @Query(nativeQuery = true, value = "SELECT c.class_Id as classId,t.termName,c.className \r\n" +
            "from classes c left join term t \r\n" +
            "on c.term_Id = t.term_Id ")
    List<Map<String, Object>> queryAllTermClassNameInfo();

}
